/*
 * Copyright 2008 p.langer
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 * 	http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package at.ac.tuwien.big.easyholdem.player;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;

import at.ac.tuwien.big.easyholdem.player.Player.Gender;

/**
 * <p>
 * A {@link PlayerDAO} implementation using Java 6 JavaDB (Derby).
 * </p>
 * 
 * <p>
 * Created on 19.01.2008 at 00:56:07.
 * </p>
 * 
 * @author Philip Langer <langer[at]big[dot]tuwien[dot]ac[dot]at>
 */
public class DerbyPlayerDAO implements PlayerDAO {

	/**
	 * Connection to the database.
	 */
	private Connection connection;

	/**
	 * JDBC driver name.
	 */
	private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

	/**
	 * Connection url.
	 */
	private static final String URL = "jdbc:derby:playerdb;create=true";

	/**
	 * Player Table name.
	 */
	private static final String TABLE_NAME = "POKERPLAYER";

	/**
	 * Player Table create statement.
	 */
	private static final String createPlayerTableStmt = "CREATE TABLE "
			+ TABLE_NAME + " (\n"
			+ "ID			BIGINT \n"
			+ "					NOT NULL \n"
			+ "					PRIMARY KEY GENERATED ALWAYS AS IDENTITY \n"
			+ "					(START WITH 1, INCREMENT BY 1), \n"
			+ "LASTNAME		VARCHAR(30),\n"
			+ "FIRSTNAME	VARCHAR(30),\n"
			+ "GENDER		VARCHAR(10),\n"
			+ "DATEOFBIRTH	DATE,\n"
			+ "PWORD		VARCHAR(30),\n"
			+ "USERNAME		VARCHAR(30),\n"
			+ "STACK		INTEGER)";

	private static final String strDelPlayerStmt = "DELETE FROM " + TABLE_NAME
			+ " WHERE ID = ?";
	private static PreparedStatement delPlayerStmt;

	private static final String strAddPlayerStmt = "INSERT INTO "
			+ TABLE_NAME
			+ " (LASTNAME, FIRSTNAME, GENDER, DATEOFBIRTH, PWORD, USERNAME, STACK) VALUES (?, ?, ?, ?, ?, ?, ?)";
	private static PreparedStatement addPlayerStmt;

	private static final String strUpdatePlayerStmt = "UPDATE "
			+ TABLE_NAME
			+ " SET LASTNAME = ?, FIRSTNAME = ?, GENDER = ?, DATEOFBIRTH = ?, PWORD = ?, USERNAME = ?, STACK = ?"
			+ " WHERE ID = ?";
	private static PreparedStatement updatePlayerStmt;

	private static final String strSelAllPlayerStmt = "SELECT * FROM "
			+ TABLE_NAME + "";
	private static PreparedStatement selAllPlayerStmt;

	private static final String strSelByIdPlayerStmt = "SELECT * FROM "
			+ TABLE_NAME + " WHERE ID = ?";
	private static PreparedStatement selByIdPlayerStmt;

	private static final String strSelByUsernamePlayerStmt = "SELECT * FROM "
			+ TABLE_NAME + " WHERE USERNAME LIKE ?";
	private static PreparedStatement selByUsernamePlayerStmt;

	/**
	 * Empty default constructor. Creates connection and prepares statements.
	 */
	public DerbyPlayerDAO() {
		connect();
		initialize();
		try {

			// manipulation statements
			delPlayerStmt = connection.prepareStatement(strDelPlayerStmt);
			addPlayerStmt = connection.prepareStatement(strAddPlayerStmt,
					Statement.RETURN_GENERATED_KEYS);
			updatePlayerStmt = connection.prepareStatement(strUpdatePlayerStmt);

			// select statements
			selAllPlayerStmt = connection.prepareStatement(strSelAllPlayerStmt);
			selByIdPlayerStmt = connection
					.prepareStatement(strSelByIdPlayerStmt);
			selByUsernamePlayerStmt = connection
					.prepareStatement(strSelByUsernamePlayerStmt);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Connects to the db.
	 */
	public void connect() {
		// load driver
		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception ex) {
			System.err.println("Der JDBC-Treiber konnte nicht "
					+ "geladen werden.");
			System.exit(1);
		}

		// establish connection
		try {
			connection = DriverManager.getConnection(URL);
		} catch (SQLException ex) {
			System.err.println("Die Verbindung zur Datenbank konnte "
					+ "nicht hergestellt werden. "
					+ "Die Fehlermeldung lautet: " + ex.getMessage());
			System.exit(1);
		}
	}

	/**
	 * Disconnects.
	 */
	public void disconnect() {
		try {
			if (connection != null && !connection.isClosed()) {
				connection.close();
				connection = null;
			}
		} catch (SQLException ex) {
			System.err.println("Die Verbindung zur Datenbank "
					+ "konnte nicht geschlossen werden. "
					+ "Die Fehlermeldung lautet: " + ex.getMessage());
			System.exit(1);
		}
	}

	/**
	 * Prints all tables to the System.out stream.
	 */
	public void showTables() {
		String query = "select tablename from sys.systables";
		String message = "Datenbankfehler. Die Fehlermeldung lautet: ";
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery(query);

			while (resultSet.next()) {
				System.out.println(resultSet.getString(1));
			}
		} catch (SQLException ex) {
			System.err.println(message + ex.getMessage());
		} finally {

			// free resources
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException ex) {
					System.err.println(message + ex.getMessage());
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException ex) {
					System.err.println(message + ex.getMessage());
				}
			}
		}
	}

	/**
	 * Extracts a {@link Player} object from the specified <code>rs</code>.
	 * 
	 * @param rs
	 *            {@link ResultSet} to read.
	 * @return extracted {@link Player} object.
	 * @throws SQLException
	 *             sql errors.
	 */
	private Player getPlayerFromRow(ResultSet rs) throws SQLException {
		Player p = new Player();
		p.setId(rs.getLong(1));
		p.setLastName(rs.getString(2));
		p.setFirstName(rs.getString(3));
		p.setGender(getGenderByString(rs.getString(4)));
		p.setDateOfBirth(rs.getDate(5));
		p.setPassword(rs.getString(6));
		p.setUserName(rs.getString(7));
		p.setStack(rs.getInt(8));
		return p;
	}

	/**
	 * Returns the {@link Gender} value by the specified <code>string</code> value.
	 * 
	 * @param string string value.
	 * @return {@link Gender} value.
	 */
	private Gender getGenderByString(String string) {
		if (Gender.FEMALE.name().equals(string)) {
			return Gender.FEMALE;
		} else {
			return Gender.MALE;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#delete(at.ac.tuwien.big.easyholdem.player.Player)
	 */
	public void delete(Player player) {
		try {
			delPlayerStmt.clearParameters();
			delPlayerStmt.setLong(1, player.getId());
			delPlayerStmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#getAll()
	 */
	public Collection<Player> getAll() {
		try {
			selAllPlayerStmt.clearParameters();
			ResultSet rs = selAllPlayerStmt.executeQuery();
			Collection<Player> players = new HashSet<Player>();
			while (rs.next()) {
				players.add(getPlayerFromRow(rs));
			}
			return players;
		} catch (SQLException e) {
			//e.printStackTrace();
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#getPlayerById(long)
	 */
	public Player getPlayerById(long id) {
		try {
			selByIdPlayerStmt.clearParameters();
			selByIdPlayerStmt.setLong(1, id);
			ResultSet rs = selByIdPlayerStmt.executeQuery();
			rs.next();
			return getPlayerFromRow(rs);
		} catch (SQLException e) {
			//e.printStackTrace();
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#getPlayerByUsername(java.lang.String)
	 */
	public Player getPlayerByUsername(String userName) {
		try {
			selByUsernamePlayerStmt.clearParameters();
			selByUsernamePlayerStmt.setString(1, userName);
			ResultSet rs = selByUsernamePlayerStmt.executeQuery();
			rs.next();
			return getPlayerFromRow(rs);
		} catch (SQLException e) {
			//e.printStackTrace();
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#persist(at.ac.tuwien.big.easyholdem.player.Player)
	 */
	public Player persist(Player player) {
		try {
			addPlayerStmt.clearParameters();
			addPlayerStmt.setString(1, player.getLastName());
			addPlayerStmt.setString(2, player.getFirstName());
			addPlayerStmt.setString(3, player.getGender().name());
			addPlayerStmt.setDate(4,
					new Date(player.getDateOfBirth().getTime()));
			addPlayerStmt.setString(5, player.getPassword());
			addPlayerStmt.setString(6, player.getUserName());
			addPlayerStmt.setInt(7, player.getStack());

			addPlayerStmt.executeUpdate();
			ResultSet rs = addPlayerStmt.getGeneratedKeys();
			rs.next();
			player.setId(rs.getLong(1));
			return player;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#update(at.ac.tuwien.big.easyholdem.player.Player)
	 */
	public void update(Player player) {
		try {
			updatePlayerStmt.clearParameters();
			updatePlayerStmt.setString(1, player.getLastName());
			updatePlayerStmt.setString(2, player.getFirstName());
			updatePlayerStmt.setString(3, player.getGender().name());
			updatePlayerStmt.setDate(4, new Date(player.getDateOfBirth()
					.getTime()));
			updatePlayerStmt.setString(5, player.getPassword());
			updatePlayerStmt.setString(6, player.getUserName());
			updatePlayerStmt.setInt(7, player.getStack());
			updatePlayerStmt.setLong(8, player.getId());

			updatePlayerStmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see at.ac.tuwien.big.easyholdem.player.PlayerDAO#initialize()
	 */
	public void initialize() {
		try {
			connection.prepareStatement(createPlayerTableStmt).execute();
		} catch (SQLException ex) {
			if (ex.getErrorCode() == 30000) {
				System.out.println("Table already exists...");
			} else {
				ex.printStackTrace();
			}
		}
	}

}
